package DBUtil;

/**
 * @(#)CONN.java
 *
 *
 * @author Sky.Wind
 * @version 1.00 2008/12/2
 */
import java.io.File;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import Tools.Log;
import XML.XML;

/*
 * 未完成 public int executeUpdata(String sql, ArrayList al)
 * 
 */

public class CNN {
	private static final XML cfg = new XML(new File("Config.xml"));// 读取配置
	private static Connection con;
	private static final String ODBCdriverName = "sun.jdbc.odbc.JdbcOdbcDriver";
	private static final String sqlDriverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private static final String ODBCurl = "jdbc:odbc:";
	private static final String SQLUrl = "jdbc:sqlserver://";
	public static final File taskDir = new File("Tasks");
	private static PreparedStatement pst;
	private static String address;
	private static int port;
	private static String database;
	private static String Username;
	private static String password;

	/**
	 * 执行SQL查询语句
	 * 
	 * @param sql
	 *            预处理SQL语句
	 * @param al
	 *            参数集合,自动将JAVA数据类型转换成SQL通用类型
	 * @return 查询结果集
	 */
	public static ResultSet executeQuery(String sql, Vector al) {
		pst = getStatement(sql, al);
		ResultSet rs = null;
		try {
			rs = pst.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * 执行更新数据库语句
	 * 
	 * @param sql
	 *            预处理SQL语句
	 * @param al
	 *            参数集合,自动将JAVA数据类型转换成SQL通用类型
	 * @return 更新数量
	 */
	public static int executeUpdata(String sql, Vector al) {
		int k = 0;
		try {
			pst = getStatement(sql, al);
			k = pst.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			k = -1;
		}
		return k;
	}

	public static XML getConfig() {
		return cfg;
	}

	/**
	 * 获取数据库的连接对象
	 */
	public static Connection getConn() {
		try {
			if (con != null && con.isClosed() == false) {
				return con;
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try {
			address = (String) cfg.GetPathValue("Serv.DB.address");
			String por = (String) cfg.GetPathValue("Serv.DB.port");
			port = Integer.parseInt(por);
			database = (String) cfg.GetPathValue("Serv.DB.database");
			Username = (String) cfg.GetPathValue("Serv.DB.username");
			password = (String) cfg.GetPathValue("Serv.DB.password");

			con = getSQLConn(address, port, database, Username, password);

		} catch (Exception e) {
			Log.print("[配置参数]读取数据库XML配置失败！");
			e.printStackTrace();
			Log.print(cfg);
		}
		return con;
	}

	/**
	 * 获取ODBC数据源连接
	 */
	public static Connection getODBCConn(String odbcName, String username, String password) {
		try {
			Log.print("[连接ODBC] ODBCName:" + odbcName + "; User:" + username + "; Password:" + password + ";");
			Class.forName(ODBCdriverName);
			con = DriverManager.getConnection(ODBCurl + odbcName, username, password);
			Log.print("连接成功!");
		} catch (Exception e) {
			Log.print("连接失败!");
			e.printStackTrace();
			con = null;
		}
		return con;
	}

	/**
	 * 获取SQL纯驱动连接
	 */
	private static Connection getSQLConn(String address, int porst, String databaseName, String username, String password) {
		try {
			Log.print("[连接SQL数据库] address:" + address + ":" + porst + "; DataBase:" + databaseName + "; User:" + username + "; PassWord:" + password + ";");
			Class.forName(sqlDriverName);
			con = DriverManager.getConnection(SQLUrl + address + ":" + porst + ";DatabaseName=" + databaseName + ";", username, password);
//			Log.print("连接成功!");
		} catch (Exception e) {
//			Log.print("连接失败!");
			e.printStackTrace();
			con = null;
		}
		return con;
	}

	/**
	 * 获得预处理SQL会话对象
	 * 
	 * @param sql
	 * @param al
	 *            (String/Integer/Double/Date)
	 * @return
	 */
	public static PreparedStatement getStatement(String sql, Vector al) {
		try {
			con = getConn();
			pst = con.prepareStatement(sql);
			for (int i = 0; i < al.size(); i++) {
				Object obj = al.get(i);
//				if (obj != null) {
				if (obj.getClass() == String.class) {// 字符串
					pst.setString(i + 1, (String) obj);
				} else if (obj.getClass() == Integer.class) {// 整数
					pst.setInt(i + 1, ((Integer) obj).intValue());
				} else if (obj.getClass() == Double.class) {// 浮点
					pst.setDouble(i + 1, ((Double) obj).doubleValue());
				} else if (obj.getClass() == Date.class) {// 浮点
					pst.setDate(i + 1, (Date) obj);// 时间
				} else if (obj.getClass() == Long.class) {// 浮点
					pst.setLong(i + 1, (Long) obj);// 时间
				} else {
					Log.print("SQL更新语句：未知参数数据类型！ ");
				}
//				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pst;
	}
}